{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Class05"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Compustat Global "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Read data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>gvkey</th>\n",
       "      <th>iid</th>\n",
       "      <th>datadate</th>\n",
       "      <th>ajexdi</th>\n",
       "      <th>cshoc</th>\n",
       "      <th>prccd</th>\n",
       "      <th>trfd</th>\n",
       "      <th>isin</th>\n",
       "      <th>tpci</th>\n",
       "      <th>fic</th>\n",
       "      <th>prirow</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1491</td>\n",
       "      <td>01W</td>\n",
       "      <td>19960702</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3805100.0</td>\n",
       "      <td>131.93</td>\n",
       "      <td>1.000413</td>\n",
       "      <td>IL0006320183</td>\n",
       "      <td>0</td>\n",
       "      <td>ISR</td>\n",
       "      <td>01W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1491</td>\n",
       "      <td>01W</td>\n",
       "      <td>19960703</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3805100.0</td>\n",
       "      <td>133.25</td>\n",
       "      <td>1.000413</td>\n",
       "      <td>IL0006320183</td>\n",
       "      <td>0</td>\n",
       "      <td>ISR</td>\n",
       "      <td>01W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1491</td>\n",
       "      <td>01W</td>\n",
       "      <td>19960704</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3805100.0</td>\n",
       "      <td>135.25</td>\n",
       "      <td>1.000413</td>\n",
       "      <td>IL0006320183</td>\n",
       "      <td>0</td>\n",
       "      <td>ISR</td>\n",
       "      <td>01W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1491</td>\n",
       "      <td>01W</td>\n",
       "      <td>19960708</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3805100.0</td>\n",
       "      <td>131.87</td>\n",
       "      <td>1.000413</td>\n",
       "      <td>IL0006320183</td>\n",
       "      <td>0</td>\n",
       "      <td>ISR</td>\n",
       "      <td>01W</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1491</td>\n",
       "      <td>01W</td>\n",
       "      <td>19960709</td>\n",
       "      <td>1.0</td>\n",
       "      <td>3805100.0</td>\n",
       "      <td>128.57</td>\n",
       "      <td>1.000413</td>\n",
       "      <td>IL0006320183</td>\n",
       "      <td>0</td>\n",
       "      <td>ISR</td>\n",
       "      <td>01W</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   gvkey  iid  datadate  ajexdi      cshoc   prccd      trfd          isin  \\\n",
       "0   1491  01W  19960702     1.0  3805100.0  131.93  1.000413  IL0006320183   \n",
       "1   1491  01W  19960703     1.0  3805100.0  133.25  1.000413  IL0006320183   \n",
       "2   1491  01W  19960704     1.0  3805100.0  135.25  1.000413  IL0006320183   \n",
       "3   1491  01W  19960708     1.0  3805100.0  131.87  1.000413  IL0006320183   \n",
       "4   1491  01W  19960709     1.0  3805100.0  128.57  1.000413  IL0006320183   \n",
       "\n",
       "  tpci  fic prirow  \n",
       "0    0  ISR    01W  \n",
       "1    0  ISR    01W  \n",
       "2    0  ISR    01W  \n",
       "3    0  ISR    01W  \n",
       "4    0  ISR    01W  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file_path = '/Users/ml/Google Drive/af/teaching/database/data/'\n",
    "gsecd = pd.read_csv(file_path+'g_secd.txt',sep='\\t',low_memory=False)\n",
    "gsecd.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of obs:  539761\n"
     ]
    }
   ],
   "source": [
    "print('Number of obs:',len(gsecd))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Keep common shares\n",
    "**tpci** = 0 means common share"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of obs 508329\n"
     ]
    }
   ],
   "source": [
    "gsecd_1 = gsecd[gsecd['tpci']=='0'].copy()\n",
    "print('Number of obs:',len(gsecd_1))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Keep primary share\n",
    "**prirow** indicates which is primary share"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of obs: 469425\n"
     ]
    }
   ],
   "source": [
    "gsecd_2 = gsecd_1[gsecd_1['prirow']==gsecd_1['iid']].copy()\n",
    "print('Number of obs:',len(gsecd_2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Remove missing fic"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of obs: 469198\n"
     ]
    }
   ],
   "source": [
    "gsecd_3 = gsecd_2[gsecd_2['fic'].notnull()].copy()\n",
    "print('Number of obs:',len(gsecd_3))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Number of markets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of markets: 76\n"
     ]
    }
   ],
   "source": [
    "n_mkt = len(gsecd_3['fic'].unique())\n",
    "print('Number of markets:',n_mkt)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Number of years by market"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>fic</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>ARG</th>\n",
       "      <td>1996</td>\n",
       "      <td>1996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AUS</th>\n",
       "      <td>1996</td>\n",
       "      <td>1996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AUT</th>\n",
       "      <td>1996</td>\n",
       "      <td>1996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BEL</th>\n",
       "      <td>1996</td>\n",
       "      <td>1996</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>BGR</th>\n",
       "      <td>1996</td>\n",
       "      <td>1996</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      min   max\n",
       "fic            \n",
       "ARG  1996  1996\n",
       "AUS  1996  1996\n",
       "AUT  1996  1996\n",
       "BEL  1996  1996\n",
       "BGR  1996  1996"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gsecd_3['year'] = (gsecd_3['datadate']/10000).astype(int)\n",
    "gsecd_3.groupby('fic')['year'].aggregate(['min','max']).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Number of firms by market"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fic</th>\n",
       "      <th>n</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ARG</td>\n",
       "      <td>67</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AUS</td>\n",
       "      <td>548</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AUT</td>\n",
       "      <td>88</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>BEL</td>\n",
       "      <td>112</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>BGR</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   fic    n\n",
       "0  ARG   67\n",
       "1  AUS  548\n",
       "2  AUT   88\n",
       "3  BEL  112\n",
       "4  BGR    1"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "n_firm = gsecd_3.drop_duplicates(['fic','gvkey']).groupby('fic')['gvkey'].count().to_frame('n').reset_index()\n",
    "n_firm.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Keep if number of firms is greater than 50"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of obs: 461017\n"
     ]
    }
   ],
   "source": [
    "gsecd_4 = gsecd_3.merge(n_firm,how='inner',on='fic')\n",
    "gsecd_4 = gsecd_4[gsecd_4['n']>50]\n",
    "print('Number of obs:',len(gsecd_4))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### List all markets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>fic</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>ISR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GBR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ESP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>PHL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>SGP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>JPN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>IRL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>SWE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>AUS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>FIN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>NLD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>MYS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>DNK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>ZAF</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>NOR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>NZL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>ITA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>FRA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>HKG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>BMU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>DEU</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>CHE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>AUT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>BEL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>MEX</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>CHL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>PRT</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>KOR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>THA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>ARG</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>CHN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>GRC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>IDN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>TWN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>IND</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>TUR</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>CYM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>POL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>PAK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>LKA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>CZE</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>SVK</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    fic\n",
       "0   ISR\n",
       "1   GBR\n",
       "2   ESP\n",
       "3   PHL\n",
       "4   SGP\n",
       "5   JPN\n",
       "6   IRL\n",
       "7   SWE\n",
       "8   AUS\n",
       "9   FIN\n",
       "10  NLD\n",
       "11  MYS\n",
       "12  DNK\n",
       "13  ZAF\n",
       "14  NOR\n",
       "15  NZL\n",
       "16  ITA\n",
       "17  FRA\n",
       "18  HKG\n",
       "19  BMU\n",
       "20  DEU\n",
       "21  CHE\n",
       "22  AUT\n",
       "23  BEL\n",
       "24  MEX\n",
       "25  CHL\n",
       "26  PRT\n",
       "27  KOR\n",
       "28  THA\n",
       "29  ARG\n",
       "30  CHN\n",
       "31  GRC\n",
       "32  IDN\n",
       "33  TWN\n",
       "34  IND\n",
       "35  TUR\n",
       "36  CYM\n",
       "37  POL\n",
       "38  PAK\n",
       "39  LKA\n",
       "40  CZE\n",
       "41  SVK"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gsecd_4.drop_duplicates('fic')[['fic']].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Adjusted price\n",
    "$$p_{adj} = \\frac{prccd}{ajexdi}\\times trfd$$"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "gsecd_4['p_adj'] = gsecd_4['prccd'] / gsecd_4['ajexdi'] * gsecd_4['trfd']\n",
    "# deal with infinity value in case ajexdi = 0\n",
    "gsecd_4['p_adj'] = np.where((gsecd_4['p_adj']==np.inf)|(gsecd_4['p_adj']==-np.inf),np.nan,gsecd_4['p_adj'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Bloomberg"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>GB00B1XZS820 Equity</th>\n",
       "      <th>GB0006731235 Equity</th>\n",
       "      <th>GB00B02J6398 Equity</th>\n",
       "      <th>GB0000536739 Equity</th>\n",
       "      <th>GB0000456144 Equity</th>\n",
       "      <th>GB0002162385 Equity</th>\n",
       "      <th>GB0009895292 Equity</th>\n",
       "      <th>GB0002634946 Equity</th>\n",
       "      <th>GB0031348658 Equity</th>\n",
       "      <th>...</th>\n",
       "      <th>GB0007669376 Equity</th>\n",
       "      <th>GB00B1FH8J72 Equity</th>\n",
       "      <th>GB0008847096 Equity</th>\n",
       "      <th>DE000TUAG000 Equity</th>\n",
       "      <th>GB0008782301 Equity</th>\n",
       "      <th>GB00B10RZP78 Equity</th>\n",
       "      <th>GB00B39J2M42 Equity</th>\n",
       "      <th>GB00BH4HKS39 Equity</th>\n",
       "      <th>JE00B8KF9B49 Equity</th>\n",
       "      <th>GB00B1KJJ408 Equity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1995-12-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3380</td>\n",
       "      <td>NaN</td>\n",
       "      <td>100.2</td>\n",
       "      <td>589.7778</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7892.1128</td>\n",
       "      <td>11846.6920</td>\n",
       "      <td>168826</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3689.5000</td>\n",
       "      <td>5908</td>\n",
       "      <td>8130.6519</td>\n",
       "      <td>1285.9</td>\n",
       "      <td>23519.7598</td>\n",
       "      <td>3607.0000</td>\n",
       "      <td>1719.9761</td>\n",
       "      <td>1881.2000</td>\n",
       "      <td>3252.5000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1996-12-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3647</td>\n",
       "      <td>NaN</td>\n",
       "      <td>186.0</td>\n",
       "      <td>675.6567</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8576.5000</td>\n",
       "      <td>13250.6925</td>\n",
       "      <td>186002</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3919.5000</td>\n",
       "      <td>6216</td>\n",
       "      <td>7971.6686</td>\n",
       "      <td>1253.2</td>\n",
       "      <td>25127.4228</td>\n",
       "      <td>5466.3999</td>\n",
       "      <td>2142.4644</td>\n",
       "      <td>1894.8000</td>\n",
       "      <td>3534.8999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1997-12-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3987</td>\n",
       "      <td>NaN</td>\n",
       "      <td>250.5</td>\n",
       "      <td>1073.5072</td>\n",
       "      <td>91481.0</td>\n",
       "      <td>8256.4800</td>\n",
       "      <td>12887.3600</td>\n",
       "      <td>232429</td>\n",
       "      <td>...</td>\n",
       "      <td>2131.0</td>\n",
       "      <td>4110.6001</td>\n",
       "      <td>6622</td>\n",
       "      <td>7626.6794</td>\n",
       "      <td>1360.4</td>\n",
       "      <td>19674.0000</td>\n",
       "      <td>5658.2002</td>\n",
       "      <td>3417.9853</td>\n",
       "      <td>1979.3000</td>\n",
       "      <td>3842.3999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1998-12-31</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4014</td>\n",
       "      <td>NaN</td>\n",
       "      <td>414.2</td>\n",
       "      <td>1664.9763</td>\n",
       "      <td>105864.0</td>\n",
       "      <td>8977.8950</td>\n",
       "      <td>15018.4750</td>\n",
       "      <td>219494</td>\n",
       "      <td>...</td>\n",
       "      <td>2817.8</td>\n",
       "      <td>4718.5000</td>\n",
       "      <td>7438</td>\n",
       "      <td>8394.1866</td>\n",
       "      <td>1443.1</td>\n",
       "      <td>21520.0000</td>\n",
       "      <td>6207.3999</td>\n",
       "      <td>3894.7934</td>\n",
       "      <td>2452.5000</td>\n",
       "      <td>4119.8999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1999-12-31</td>\n",
       "      <td>26597.0</td>\n",
       "      <td>3703</td>\n",
       "      <td>NaN</td>\n",
       "      <td>543.7</td>\n",
       "      <td>2286.3169</td>\n",
       "      <td>183184.0</td>\n",
       "      <td>19816.0000</td>\n",
       "      <td>28173.3440</td>\n",
       "      <td>254793</td>\n",
       "      <td>...</td>\n",
       "      <td>4069.7</td>\n",
       "      <td>4718.5000</td>\n",
       "      <td>8699</td>\n",
       "      <td>15235.8330</td>\n",
       "      <td>1561.2</td>\n",
       "      <td>27858.9658</td>\n",
       "      <td>6637.8999</td>\n",
       "      <td>5443.3167</td>\n",
       "      <td>3234.3999</td>\n",
       "      <td>4082.6001</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 102 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        date  GB00B1XZS820 Equity  GB0006731235 Equity  GB00B02J6398 Equity  \\\n",
       "0 1995-12-31                  NaN                 3380                  NaN   \n",
       "1 1996-12-31                  NaN                 3647                  NaN   \n",
       "2 1997-12-31                  NaN                 3987                  NaN   \n",
       "3 1998-12-31                  NaN                 4014                  NaN   \n",
       "4 1999-12-31              26597.0                 3703                  NaN   \n",
       "\n",
       "   GB0000536739 Equity  GB0000456144 Equity  GB0002162385 Equity  \\\n",
       "0                100.2             589.7778                  NaN   \n",
       "1                186.0             675.6567                  NaN   \n",
       "2                250.5            1073.5072              91481.0   \n",
       "3                414.2            1664.9763             105864.0   \n",
       "4                543.7            2286.3169             183184.0   \n",
       "\n",
       "   GB0009895292 Equity  GB0002634946 Equity  GB0031348658 Equity  \\\n",
       "0            7892.1128           11846.6920               168826   \n",
       "1            8576.5000           13250.6925               186002   \n",
       "2            8256.4800           12887.3600               232429   \n",
       "3            8977.8950           15018.4750               219494   \n",
       "4           19816.0000           28173.3440               254793   \n",
       "\n",
       "          ...           GB0007669376 Equity  GB00B1FH8J72 Equity  \\\n",
       "0         ...                           NaN            3689.5000   \n",
       "1         ...                           NaN            3919.5000   \n",
       "2         ...                        2131.0            4110.6001   \n",
       "3         ...                        2817.8            4718.5000   \n",
       "4         ...                        4069.7            4718.5000   \n",
       "\n",
       "   GB0008847096 Equity  DE000TUAG000 Equity  GB0008782301 Equity  \\\n",
       "0                 5908            8130.6519               1285.9   \n",
       "1                 6216            7971.6686               1253.2   \n",
       "2                 6622            7626.6794               1360.4   \n",
       "3                 7438            8394.1866               1443.1   \n",
       "4                 8699           15235.8330               1561.2   \n",
       "\n",
       "   GB00B10RZP78 Equity  GB00B39J2M42 Equity  GB00BH4HKS39 Equity  \\\n",
       "0           23519.7598            3607.0000            1719.9761   \n",
       "1           25127.4228            5466.3999            2142.4644   \n",
       "2           19674.0000            5658.2002            3417.9853   \n",
       "3           21520.0000            6207.3999            3894.7934   \n",
       "4           27858.9658            6637.8999            5443.3167   \n",
       "\n",
       "   JE00B8KF9B49 Equity  GB00B1KJJ408 Equity  \n",
       "0            1881.2000            3252.5000  \n",
       "1            1894.8000            3534.8999  \n",
       "2            1979.3000            3842.3999  \n",
       "3            2452.5000            4119.8999  \n",
       "4            3234.3999            4082.6001  \n",
       "\n",
       "[5 rows x 102 columns]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bb = pd.read_excel(file_path+'bloomberg_data.xlsx',sheet_name='Sheet3')\n",
    "bb.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Remove firms without any valid data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [],
   "source": [
    "bb_1 = bb.select_dtypes(exclude='object')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Transpose data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>variable</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1995-12-31</td>\n",
       "      <td>GB00B1XZS820 Equity</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1996-12-31</td>\n",
       "      <td>GB00B1XZS820 Equity</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1997-12-31</td>\n",
       "      <td>GB00B1XZS820 Equity</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1998-12-31</td>\n",
       "      <td>GB00B1XZS820 Equity</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1999-12-31</td>\n",
       "      <td>GB00B1XZS820 Equity</td>\n",
       "      <td>26597.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date             variable    value\n",
       "0 1995-12-31  GB00B1XZS820 Equity      NaN\n",
       "1 1996-12-31  GB00B1XZS820 Equity      NaN\n",
       "2 1997-12-31  GB00B1XZS820 Equity      NaN\n",
       "3 1998-12-31  GB00B1XZS820 Equity      NaN\n",
       "4 1999-12-31  GB00B1XZS820 Equity  26597.0"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bb_2 = pd.melt(bb_1,id_vars='date',value_vars=bb_1.columns[1:])\n",
    "bb_2.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>isin</th>\n",
       "      <th>date</th>\n",
       "      <th>asset</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CH0198251305</td>\n",
       "      <td>1995-12-31</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CH0198251305</td>\n",
       "      <td>1996-12-31</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CH0198251305</td>\n",
       "      <td>1997-12-31</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CH0198251305</td>\n",
       "      <td>1998-12-31</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CH0198251305</td>\n",
       "      <td>1999-12-31</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CH0198251305</td>\n",
       "      <td>2000-12-31</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CH0198251305</td>\n",
       "      <td>2001-12-31</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>CH0198251305</td>\n",
       "      <td>2002-12-31</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>CH0198251305</td>\n",
       "      <td>2003-12-31</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>CH0198251305</td>\n",
       "      <td>2004-12-31</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>CH0198251305</td>\n",
       "      <td>2005-12-31</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>CH0198251305</td>\n",
       "      <td>2006-12-31</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>CH0198251305</td>\n",
       "      <td>2007-12-31</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>CH0198251305</td>\n",
       "      <td>2008-12-31</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>CH0198251305</td>\n",
       "      <td>2009-12-31</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>CH0198251305</td>\n",
       "      <td>2010-12-31</td>\n",
       "      <td>7185.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>CH0198251305</td>\n",
       "      <td>2011-12-31</td>\n",
       "      <td>7243.5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>CH0198251305</td>\n",
       "      <td>2012-12-31</td>\n",
       "      <td>7250.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>CH0198251305</td>\n",
       "      <td>2013-12-31</td>\n",
       "      <td>7274.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>CH0198251305</td>\n",
       "      <td>2014-12-31</td>\n",
       "      <td>6879.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            isin       date   asset\n",
       "0   CH0198251305 1995-12-31     NaN\n",
       "1   CH0198251305 1996-12-31     NaN\n",
       "2   CH0198251305 1997-12-31     NaN\n",
       "3   CH0198251305 1998-12-31     NaN\n",
       "4   CH0198251305 1999-12-31     NaN\n",
       "5   CH0198251305 2000-12-31     NaN\n",
       "6   CH0198251305 2001-12-31     NaN\n",
       "7   CH0198251305 2002-12-31     NaN\n",
       "8   CH0198251305 2003-12-31     NaN\n",
       "9   CH0198251305 2004-12-31     NaN\n",
       "10  CH0198251305 2005-12-31     NaN\n",
       "11  CH0198251305 2006-12-31     NaN\n",
       "12  CH0198251305 2007-12-31     NaN\n",
       "13  CH0198251305 2008-12-31     NaN\n",
       "14  CH0198251305 2009-12-31     NaN\n",
       "15  CH0198251305 2010-12-31  7185.0\n",
       "16  CH0198251305 2011-12-31  7243.5\n",
       "17  CH0198251305 2012-12-31  7250.1\n",
       "18  CH0198251305 2013-12-31  7274.8\n",
       "19  CH0198251305 2014-12-31  6879.0"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "bb_3 = bb_2.copy()\n",
    "bb_3.columns = ['date','isin','asset']\n",
    "bb_3['isin'] = bb_3['isin'].str[:12]\n",
    "bb_3 = bb_3[['isin','date','asset']]\n",
    "bb_3 = bb_3.sort_values(['isin','date']).reset_index(drop=True)\n",
    "bb_3.head(20)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
